"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 人为因素类
Case Name   : 时间跳变后，查看数据库状态并进行dml操作
Create At   : 2020-10-27
Owner       : n@ningyali
Description :
    1、 登录主节点，在root用户下执行date -s xx:xx:xx，改变系统时间；
    2、 gs_om -t status --detail，查看主备状态
    3、 gsql方式进入数据库，创建测试表test，插入语句”
    insert into test values(3,now());”，查看数据是否是跳变后的时间；
    4、 登录备节点，进入数据库，查看test表中数据是否和主机保持一致；
    5、 登录备节点，在root用户下执行date -s xx:xx:xx，改变系统时间；
    6、 gs_om -t status --detail，查看主备状态；
    7、 gsql方式进入主节点数据库，创建测试表test，插入语句”
    insert into test values(2,now());”，查看数据是否是跳变后的时间；
    8、 登录备节点，进入数据库，查看test表中数据是否和主机保持一致
Expect      :
    1、 主节点系统时间修改成功
    2、 主备状态正常
    3、 数据时间显示正确
    4、 备节点数据与主节点保持一致
    5、 备节点时间修改成功
    6、 主备状态正常
    7、 数据时间显示正确，为主节点数据库时间
    8、 备节点数据与主节点保持一致
History     :
    created： 2020/10/27  created by n@ningyali
    modified：2021/05/17  modified by @wan005: 增加等待主备一致
    modified：2021/05/26  modified by @wan005: 增加等待主备一致
"""

import unittest
import time
from datetime import date, timedelta
from yat.test import Node
from testcase.utils.CommonSH import *
from testcase.utils.Common import *
from testcase.utils.Constant import *


class ChangeTimeAndDMLExcute(unittest.TestCase):
    primary_sh = CommonSH('PrimaryDbUser')
    sta1_sh = CommonSH('Standby1DbUser')
    sta2_sh = CommonSH('Standby2DbUser')
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Human_Case020:开始执行------------')
        self.primary_root_node = Node(node='PrimaryRoot')
        self.primary_user_node = Node(node='PrimaryDbUser')
        self.standby1_root_node = Node(node='Standby1Root')
        self.standby1_user_node = Node(node='Standby1DbUser')
        self.standby2_root_node = Node(node='Standby2Root')
        self.standby2_user_node = Node(node='Standby2DbUser')
        self.com = Common()
        self.Constant = Constant()
        self.t_name = 'test_humanfactor020'
        self.dump_file_name = 'test_humanfactor019.sql'

    def test_main(self):
        self.log.info("---------------登录主节点，在root用户下执行date -s xx:xx:xx，改变系统时间----------------")
        # 设置为前一天0时0分0秒
        date_str = date.today() + timedelta(days=-1)
        str_year = time.strftime("%Y", time.localtime())
        str_month = time.strftime("%b", time.localtime())
        str_day = time.strftime("%d", time.localtime())
        str_last_day = str(int(str_day) - 1)
        shell_cmd = f"date -s '{date_str} 00:00:00'"
        self.log.info(shell_cmd)
        msg = self.primary_root_node.sh(shell_cmd).result()
        self.log.info(msg)
        self.assertIn(str_year, msg)
        self.assertIn(str_month, msg)
        self.assertIn(str_last_day, msg)

        self.log.info("---------------查看主备状态----------------")
        db_status = self.primary_sh.getDbClusterStatus('status')
        self.assertTrue(db_status)

        self.log.info("-------------gsql方式进入数据库，创建测试表，插入语句---------------")
        sql_cmd = f"drop table if exists {self.t_name}; " \
            f"create table {self.t_name} (id int, time varchar(50));" \
            f"insert into {self.t_name} values (3, now())"
        self.log.info(sql_cmd)
        msg = self.primary_sh.executDbSql(sql_cmd)
        self.log.info(msg)
        for err_msg in self.Constant.SQL_WRONG_MSG:
            self.assertNotIn(err_msg, msg)

        self.log.info("-------------查看数据是否是跳变后的时间---------------")
        select_cmd = f"select * from {self.t_name} where id=3;"
        self.log.info(select_cmd)
        msg = self.primary_sh.executDbSql(select_cmd)
        self.log.info(msg)
        self.assertIn(str(date_str), msg)

        self.log.info("------------等待主备一致---------")
        result = self.sta1_sh.check_data_consistency()
        self.assertTrue(result)
        result = self.sta2_sh.check_data_consistency()
        self.assertTrue(result)

        self.log.info("-------------查看备节点test表中数据是否和主机保持一致---------------")
        select_cmd = f"select * from {self.t_name};"
        nodes_tuple = (self.primary_user_node, self.standby1_user_node, self.standby2_user_node)
        flag = self.com.check_data_sample_by_all(select_cmd, *nodes_tuple)
        self.assertTrue(flag)

        self.log.info("---------------登录备节点，在root用户下执行date -s xx:xx:xx，改变系统时间----------------")
        shell_cmd = f"date -s '{date_str} 00:00:00'"
        self.log.info(shell_cmd)
        msg = self.standby1_root_node.sh(shell_cmd).result()
        self.log.info(msg)
        self.assertIn(str_year, msg)
        self.assertIn(str_month, msg)
        self.assertIn(str_last_day, msg)

        self.log.info("---------------查看主备状态----------------")
        db_status = self.primary_sh.getDbClusterStatus('status')
        self.assertTrue(db_status)

        self.log.info("-------------gsql方式进入主节点数据库，创建测试表, 插入语句---------------")
        sql_cmd = f"drop table if exists {self.t_name}; " \
            f"create table {self.t_name} (id int, time varchar(50));" \
            f"insert into {self.t_name} values (2, now())"
        self.log.info(sql_cmd)
        msg = self.primary_sh.executDbSql(sql_cmd)
        self.log.info(msg)
        for err_msg in self.Constant.SQL_WRONG_MSG:
            self.assertNotIn(err_msg, msg)

        self.log.info("---------------查看数据是否是跳变后的时间----------------")
        select_cmd = f"select * from {self.t_name} where id=2;"
        self.log.info(select_cmd)
        msg = self.primary_sh.executDbSql(select_cmd)
        self.log.info(msg)
        self.assertIn(str(date_str), msg)

        self.log.info("------------等待主备一致---------")
        result = self.sta1_sh.check_data_consistency()
        self.assertTrue(result)
        result = self.sta2_sh.check_data_consistency()
        self.assertTrue(result)

        self.log.info("-------------查看备节点test表中数据是否和主机保持一致---------------")
        select_cmd = f"select * from {self.t_name};"
        flag = self.com.check_data_sample_by_all(select_cmd, *nodes_tuple)
        self.assertTrue(flag)

    def tearDown(self):
        drop_cmd = f'''drop table if exists {self.t_name};'''
        self.log.info(drop_cmd)
        msg = self.primary_sh.executDbSql(drop_cmd)
        self.log.info(msg)

        time_str = time.strftime("%H:%M:%S", time.localtime(time.time()))
        date_str = date.today()
        shell_cmd = f"date -s '{time_str} {date_str}'"
        self.log.info(shell_cmd)
        pri_msg = self.primary_root_node.sh(shell_cmd).result()
        self.log.info(pri_msg)
        sta_msg = self.standby1_root_node.sh(shell_cmd).result()
        self.log.info(sta_msg)
        self.log.info(self.primary_root_node.sh('date').result())
        self.log.info('------------Opengauss_Reliability_Human_Case020:执行完成------------')
